const mysql = require('../db/mysql57');

const userDao = {
    getUserInfo(reqId, uid) {
        let sql = `select
                u.id as id,
                u.userTypeId as userTypeId,
                ut.name as userType,
                u.name as name,
                u.phone as phone,
                u.account as account
            from ( select * from User where id = ? and is_del = ? ) as u
            left join UserType as ut on u.userTypeId = ut.id`;
        let data = [ uid, 'false' ];
        return mysql.sqlExec(sql, data);
    },
    getUserInfoAndPassword(reqId, uid) {
        let sql = `select
                u.id as id,
                u.userTypeId as userTypeId,
                ut.name as userType,
                u.name as name,
                u.phone as phone,
                u.account as account,
                u.password as password
            from ( select * from User where id = ? and is_del = ? ) as u
            left join UserType as ut on u.userTypeId = ut.id`;
        let data = [ uid, 'false' ];
        return mysql.sqlExec(sql, data);
    },
    addUser(reqId, userType, name, phone, account, password) {
        let sql = `INSERT INTO User (userTypeId, name, phone, account, password) VALUES ((
                select id from UserType where name = ?
            ), ?, ?, ?, ?)`;
        let data = [ userType, name, phone, account, password ];
        return mysql.sqlExec(sql, data);
    },
    getPower(reqId, uid) {
        let sql = `select
                up.id as id,
                p.id as powerId,
                p.name as name
            from (
                select id, powerId from UserPower where userId = ?
            ) as up
            left join Power as p on up.powerId = p.id`;
        let data = [ uid ];
        return mysql.sqlExec(sql, data);
    },
    getAllUser(reqId, uid) {
        let sql = `select
                u.id as id,
                u.userTypeId as userTypeId,
                ut.name as userType,
                u.name as name,
                u.phone as phone,
                u.account as account
            from User as u
            left join UserType as ut on u.userTypeId = ut.id
            where u.is_del = ?
            order by u.id desc`;
        let data = [ 'false' ];
        return mysql.sqlExec(sql, data);
    },
    getAllUser_usePower(reqId, uid) {
        let sql = `select
                u.id as id,
                u.userTypeId as userTypeId,
                ut.name as userType,
                u.name as name,
                u.phone as phone,
                u.account as account
            from User as u
            left join UserType as ut on u.userTypeId = ut.id
            where u.is_del = ? AND ut.name != ? AND ut.name != ?
            order by u.id desc`;
        let data = [ 'false', 'root', 'sub-root' ];
        return mysql.sqlExec(sql, data);
    },
    getAllUserAndPassword(reqId, uid) {
        let sql = `select
                u.id as id,
                u.userTypeId as userTypeId,
                ut.name as userType,
                u.name as name,
                u.phone as phone,
                u.account as account,
                u.password as password
            from User as u
            left join UserType as ut on u.userTypeId = ut.id
            where u.is_del = ?
            order by u.id desc`;
        let data = [ 'false' ];
        return mysql.sqlExec(sql, data);
    },
    getAllUserAndPassword_usePower(reqId, uid) {
        let sql = `select
                u.id as id,
                u.userTypeId as userTypeId,
                ut.name as userType,
                u.name as name,
                u.phone as phone,
                u.account as account,
                u.password as password
            from User as u
            left join UserType as ut on u.userTypeId = ut.id
            where u.is_del = ? AND ut.name != ? AND ut.name != ?
            order by u.id desc`;
        let data = [ 'false', 'root', 'sub-root' ];
        return mysql.sqlExec(sql, data);
    },
    async deleteUser(reqId, uid, userId) {
        let connection, ret, result;
        if (uid == userId) {
            throw new Error("不能删除当前登录的用户");
        }
        // 开启事务
        try {
            connection = await mysql.startTransaction();
        } catch (err) {
            console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 开启事务失败`);
            await mysql.rollbackTransaction(connection);
            throw new Error("连接数据库失败 请联系系统管理员");
        }
        console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 开启事务成功`);
        // 查询这个用户有没有负责科室
        try {
            let sql = `select count(id) as s from Department where adminId = ? and is_del = ?`;
            let data = [ userId, 'false' ];
            result = await mysql.sqlExecInTransaction(connection, sql, data);
        } catch (err) {
            console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 查询用户是否负责科室失败 ${ err.message }`);
            await mysql.rollbackTransaction(connection);
            throw new Error("查询用户是否负责科室失败");
        }
        console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 查询用户是否负责科室成功 ${ JSON.stringify(result) }`);
        if (result[0].s > 0) {
            throw new Error("被删除用户还有负责的科室 请先进行相关处理");
        }
        try {
            let sql = `update User set is_del = ?, del_pk = ? where id = ?`;
            let data = [ 'true', userId, userId ];
            ret = await mysql.sqlExecInTransaction(connection, sql, data);
        } catch (err) {
            console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 删除用户失败 ${ err.message }`);
            await mysql.rollbackTransaction(connection);
            throw new Error("删除用户失败 请稍后再试 或 联系管理员排查问题");
        }
        console.log(`[ INFO ] [ req-id = ${ reqId } ] [ Dao ] 删除用户成功`);
        // 所有操作成功 提交事务
        await mysql.commitTransaction(connection);
        return ret;
    },
    updateUserInfo(reqId, uid, id, name, phone) {
        let sql = `update User set name = ?, phone = ? where id = ? and is_del = ?`;
        let data = [ name, phone, id, 'false' ];
        return mysql.sqlExec(sql, data);
    },
    updateUserInfoAndPassword(reqId, uid, id, name, phone, password) {
        let sql = `update User set name = ?, phone = ?, password = ? where id = ? and is_del = ?`;
        let data = [ name, phone, password, id, 'false' ];
        return mysql.sqlExec(sql, data);
    },
    updateSelfInfoAndPassword(reqId, uid, name, phone, password) {
        let sql = `update User set name = ?, phone = ?, password = ? where id = ?`;
        let data = [ name, phone, password, uid ];
        return mysql.sqlExec(sql, data);
    },
    getUserDepartment(reqId, uid, userId) {
        let sql = `select
                d.id as id,
                d.hospital as hospital,
                d.name as name,
                c.name as city,
                p.name as province
            from (
                select * from Department where adminId = ?
            ) as d
            left join City as c on d.cityId = c.id
            left join Province as p on c.provinceId = p.id`;
        let data = [ userId ];
        return mysql.sqlExec(sql, data);
    },
    getUserType(reqId, uid) {
        let sql = `select
                ut.name as userType
            from ( select * from User where id = ? ) as u
            left join UserType as ut on u.userTypeId = ut.id`;
        let data = [ uid ];
        return mysql.sqlExec(sql, data);
    },
    getSelfPowerList(reqId, uid) {
        let sql = `select
                p.name as power
            from ( select id, powerId from UserPower where userId = ? ) as up
            left join Power as p on up.powerId = p.id`;
        let data = [ uid ];
        return mysql.sqlExec(sql, data);
    }
};

module.exports = userDao;